package cn.jhc.db;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.jhc.bean.Song;
import cn.jhc.bean.User;

public class SongDao {

	private static String search = "";

	public static String getSearch() {
		return search;
	}

	public static void setSearch(String search) {
		SongDao.search = search;
	}

	public static void addSong(DataSource dataSource, Song song) {
		Connection connection = null;
		try {

			QueryRunner runner = new QueryRunner(dataSource);
			String sql = "insert into song(songname,songsinger,songimage,songwords,songURL) "
					+ " values(?,?,?,?,?)";
			runner.insert(sql, new BeanHandler<>(Song.class),
					song.getSongname(), song.getSongsinger(),
					song.getSongimg(), song.getSongwords(), song.getSongURL());
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(connection);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}
		
	public static List<Song> findAllsong(DataSource dataSource) {
		List<Song> list = null;
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner(dataSource);
			String sql = "select * from song";
			ResultSetHandler<List<Song>> h = new BeanListHandler<>(Song.class);
			list = runner.query(sql, h);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	public static List<Song> searchSong(DataSource dataSource) {
		List<Song> list = null;
		Connection conn = null;
		try {
			QueryRunner runner = new QueryRunner(dataSource);
			String sql = "select * from song where songname like ?";
			Object[] params = new Object[] { search };
			System.out.println(search);
			ResultSetHandler<List<Song>> h = new BeanListHandler<>(Song.class);
			list = runner.query(sql, h, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	public static void deleteAllSong(DataSource dataSource) throws SQLException {
		QueryRunner runner = new QueryRunner(dataSource);
		runner.update("delete from song");
	}

}
